using System;
using System.Collections.Generic;
using System.Text;
using DTO;
using System.Data;
using System.Data.SqlClient;

namespace DAO
{
	/// <summary> 
	///Author: nnamthach@gmail.com 
	/// <summary>
    public class LookupDataAccess : Connection
    {
		#region Local Variable
        private string _strSPInsertName = "dbo.[procLookup_add]";
        private string _strSPUpdateName = "dbo.[procLookup_update]";
        private string _strSPDeleteName = "dbo.[procLookup_delete]";
        private string _strSPGetName = "dbo.[procLookup_get]";
        private string _strSPGetAllName = "dbo.[procLookup_getall]";
		private string _strSPGetPages = "dbo.[procLookup_getpaged]";
		private string _strSPIsExist = "dbo.[procLookup_isexist]";
        private string _strTableName = "[Lookup]";
		private string _strSPGetTransferOutName = "dbo.[procLookup_gettransferout]";
		#endregion Local Variable
		
		#region Method
        public LookupInfo Get(
        String LOOKUP_CATEGORY,
        String CODE,
		ref string sErr)
        {
			LookupInfo objEntr = new LookupInfo();
			connect();
			InitSPCommand(_strSPGetName);              
            AddParameter(LookupInfo.Field.LOOKUP_CATEGORY.ToString(), LOOKUP_CATEGORY);
            AddParameter(LookupInfo.Field.CODE.ToString(), CODE);
            
            DataTable list = new DataTable();
            try
            {
                list = executeSelectSP(command);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();  
            
            if (list.Rows.Count > 0)
                objEntr = (LookupInfo)GetDataFromDataRow(list, 0);
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            if (sErr != "") ErrorLog.SetLog(sErr);
            return objEntr;
        }

        protected override object GetDataFromDataRow(DataTable dt, int i)
        {
            LookupInfo result = new LookupInfo();
            result.LOOKUP_CATEGORY = (dt.Rows[i][LookupInfo.Field.LOOKUP_CATEGORY.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][LookupInfo.Field.LOOKUP_CATEGORY.ToString()]));
            result.CODE = (dt.Rows[i][LookupInfo.Field.CODE.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][LookupInfo.Field.CODE.ToString()]));
            result.LOOKUP_ALT = (dt.Rows[i][LookupInfo.Field.LOOKUP_ALT.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][LookupInfo.Field.LOOKUP_ALT.ToString()]));
            result.DESCRIPTN = (dt.Rows[i][LookupInfo.Field.DESCRIPTN.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][LookupInfo.Field.DESCRIPTN.ToString()]));
            result.DESCRIPTN1 = (dt.Rows[i][LookupInfo.Field.DESCRIPTN1.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][LookupInfo.Field.DESCRIPTN1.ToString()]));
            result.CREATEDBY = (dt.Rows[i][LookupInfo.Field.CREATEDBY.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][LookupInfo.Field.CREATEDBY.ToString()]));
            result.MODIFIEDBY = (dt.Rows[i][LookupInfo.Field.MODIFIEDBY.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][LookupInfo.Field.MODIFIEDBY.ToString()]));
            result.CREATEDDATE = (dt.Rows[i][LookupInfo.Field.CREATEDDATE.ToString()] == DBNull.Value ? 0 : Convert.ToInt32(dt.Rows[i][LookupInfo.Field.CREATEDDATE.ToString()]));
            result.MODIFIEDDATE = (dt.Rows[i][LookupInfo.Field.MODIFIEDDATE.ToString()] == DBNull.Value ? 0 : Convert.ToInt32(dt.Rows[i][LookupInfo.Field.MODIFIEDDATE.ToString()]));
            result.STATUS = (dt.Rows[i][LookupInfo.Field.STATUS.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][LookupInfo.Field.STATUS.ToString()]));
           
            return result;
        }

        public DataTable GetAll(
        ref string sErr)
        {
            connect();
            InitSPCommand(_strSPGetAllName);
            DataTable list = new DataTable();
            try
            {
                list = executeSelectSP();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
		/// <summary>
        /// Return 1: Table is exist Identity Field
        /// Return 0: Table is not exist Identity Field
        /// Return -1: Erro
        /// </summary>
        /// <param name="tableName"></param>
        public Int32 Add(LookupInfo objEntr, ref string sErr)
        {
            int ret = -1;
            connect();
            InitSPCommand(_strSPInsertName);
            AddParameter(LookupInfo.Field.LOOKUP_CATEGORY.ToString(), objEntr.LOOKUP_CATEGORY);
            AddParameter(LookupInfo.Field.CODE.ToString(), objEntr.CODE);
            AddParameter(LookupInfo.Field.LOOKUP_ALT.ToString(), objEntr.LOOKUP_ALT);
            AddParameter(LookupInfo.Field.DESCRIPTN.ToString(), objEntr.DESCRIPTN);
            AddParameter(LookupInfo.Field.DESCRIPTN1.ToString(), objEntr.DESCRIPTN1);
            AddParameter(LookupInfo.Field.CREATEDBY.ToString(), objEntr.CREATEDBY);
            AddParameter(LookupInfo.Field.MODIFIEDBY.ToString(), objEntr.MODIFIEDBY);
            AddParameter(LookupInfo.Field.CREATEDDATE.ToString(), objEntr.CREATEDDATE);
            AddParameter(LookupInfo.Field.MODIFIEDDATE.ToString(), objEntr.MODIFIEDDATE);
            AddParameter(LookupInfo.Field.STATUS.ToString(), objEntr.STATUS);
          
            try
            {
                //command.ExecuteNonQuery();
                object tmp = executeSPScalar();
                if(tmp != null && tmp != DBNull.Value)
					ret = Convert.ToInt32(tmp);
				else 
					ret=0;
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();
            if (sErr != "") ErrorLog.SetLog(sErr);
			
            return ret;
        }

        public string Update(LookupInfo objEntr)
        {
            connect();
            InitSPCommand(_strSPUpdateName);
            AddParameter(LookupInfo.Field.LOOKUP_CATEGORY.ToString(), objEntr.LOOKUP_CATEGORY);
            AddParameter(LookupInfo.Field.CODE.ToString(), objEntr.CODE);
            AddParameter(LookupInfo.Field.LOOKUP_ALT.ToString(), objEntr.LOOKUP_ALT);
            AddParameter(LookupInfo.Field.DESCRIPTN.ToString(), objEntr.DESCRIPTN);
            AddParameter(LookupInfo.Field.DESCRIPTN1.ToString(), objEntr.DESCRIPTN1);
            AddParameter(LookupInfo.Field.CREATEDBY.ToString(), objEntr.CREATEDBY);
            AddParameter(LookupInfo.Field.MODIFIEDBY.ToString(), objEntr.MODIFIEDBY);
            AddParameter(LookupInfo.Field.CREATEDDATE.ToString(), objEntr.CREATEDDATE);
            AddParameter(LookupInfo.Field.MODIFIEDDATE.ToString(), objEntr.MODIFIEDDATE);
            AddParameter(LookupInfo.Field.STATUS.ToString(), objEntr.STATUS);
               
            string sErr = "";
            try
            {
                excuteSPNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }

        public string Delete(
        String LOOKUP_CATEGORY,
        String CODE
		)
        {
            connect();
            InitSPCommand(_strSPDeleteName);
            AddParameter(LookupInfo.Field.LOOKUP_CATEGORY.ToString(), LOOKUP_CATEGORY);
            AddParameter(LookupInfo.Field.CODE.ToString(), CODE);
              
            string sErr = "";
            try
            {
                excuteSPNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }   
		
		public DataTableCollection Get_Page(LookupInfo obj, string orderBy, int pageIndex, int pageSize, ref String sErr)
        {
			string whereClause = CreateWhereClause(obj);
            DataTableCollection dtList = null;
            connect();
            InitSPCommand(_strSPGetPages); 
          
            AddParameter("WhereClause", whereClause);
            AddParameter("OrderBy", orderBy);
            AddParameter("PageIndex", pageIndex);
            AddParameter("PageSize", pageSize);
            
            try
            {
                dtList = executeCollectSelectSP();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();
            if (sErr != "") ErrorLog.SetLog(sErr);
            return dtList;
        }
        
        public Boolean IsExist(
        String LOOKUP_CATEGORY,
        String CODE
		)
        {
            connect();
            InitSPCommand(_strSPIsExist);
            AddParameter(LookupInfo.Field.LOOKUP_CATEGORY.ToString(), LOOKUP_CATEGORY);
            AddParameter(LookupInfo.Field.CODE.ToString(), CODE);
              
            string sErr = "";
            DataTable list = new DataTable();
            try
            {
                list = executeSelectSP();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();
            if (sErr != "") ErrorLog.SetLog(sErr);
            if(list.Rows.Count==1)
				return true;
            return false;
        }
		
		private string CreateWhereClause(LookupInfo obj)
        {
            String result = "";

            return result;
        }
        
        public DataTable Search(string columnName, string columnValue, string condition, string tableName, ref string sErr)
        {
            string query = "select * from " + _strTableName + " where " + columnName + " " + condition + " " + columnValue;
            DataTable list = new DataTable();
            connect();
            try
            {
                list = executeSelectQuery(query);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            //    if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
		public DataTable GetTransferOut(string dtb, object from, object to, ref string sErr)
        {
            connect();
            InitSPCommand(_strSPGetTransferOutName);
			AddParameter("DB", dtb);
			AddParameter("FROM", from);
			AddParameter("TO", to);
            DataTable list = new DataTable();
            try
            {
                list = executeSelectSP();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
		#endregion Method
     
    }
}
